##//Z_CAWN_I_58448_20221104080000_O_GHG-FLD-CO2CO-CRDS-S024

import pandas as pd
import sqlalchemy
import bz2
import shutil

def unzip_bz2(bz2_file, output_file):
    with bz2.open(bz2_file, 'rb') as f_in:
        with open(output_file, 'wb') as f_out:
            shutil.copyfileobj(f_in, f_out)

def read_p1302(fwf_path):
    # Read CSV file
    # Define the widths of each column based on fixed-width format
    p1302_width = [26] * 58
    
    df = pd.read_fwf(fwf_path, widths=p1302_width)
    df['E_TIME'] = pd.to_datetime(df.iloc[:, 0].astype(str) + ' ' + df.iloc[:, 1].astype(str) + ' +08:00', utc=True)
    # Preprocess column names
    sanitized_columns = [col.replace('(', '').replace(')', '') for col in df.columns]
    #sanitized_columns = ['otime' if col == 'time' else col for col in sanitized_columns]
    
    # Rename columns
    df.columns = sanitized_columns
    
    print(df.columns)
    
    #data_types = {col: sqlalchemy.String(10) if i < 2 else sqlalchemy.Double for i, col in enumerate(df.columns[:-1])}
    dtype_mapping = {'E_TIME': sqlalchemy.DateTime, 'DATE': sqlalchemy.String(8), 'TIME': sqlalchemy.String(10)}
    for i in range(2, len(df.columns)-1):
        dtype_mapping[df.columns[i]] = sqlalchemy.Double
    # Create MySQL table
    # Assuming you have a MySQL connection established using a library like pymysql
    # Adjust the following code to fit your database setup
    
    # Define the MySQL connection string
    engine = sqlalchemy.create_engine('mysql+mysqlconnector://grafana:grafana@localhost/grafanadb')
    
    
    #cursor = conn.cursor()
    
    # Construct CREATE TABLE query
    #create_table_query = f"CREATE TABLE rdt_1302 ({', '.join([f'{col} {data_types[col]}' for col in df.columns])})"
    
    # Execute query
    #cursor.execute(create_table_query)
    print(df)
    # Insert data into MySQL table
    # Assuming your_table already exists in your MySQL database
    df.to_sql('rdt_1302', con=engine, if_exists='append', index=False, dtype=dtype_mapping)
    
    # Commit changes and close connection
    engine.dispose()
    
if __name__ == "__main__":
    bz2_file = "Z_CAWN_I_58448_20221104100000_O_GHG-FLD-CO2CO-CRDS-S024.bz2"  # Specify the path to your bzip2 file
    output_file = "unzipped_file.txt"  # Specify the path for the output file
    
    unzip_bz2(bz2_file, output_file)
    print("File decompressed successfully.")

    read_p1302(output_file)
